<?php

namespace App\Service\Work;

use App\Models\Works\ArticleModel;

use DB;
class BestWorksService
{
    public static function getBestWorks($uid, $page, $offset)
    {
        $data =  DB::select("select
(select count(*) from lsj_comment_log b where a.id=b.worksid) as comment_num,
a.id,a.title,a.introduction,a.thumb,a.create_time,a.reading,
a.recommend
from lsj_article a
where a.status=1 and cid=1
order by a.recommend,a.reading desc,a.create_time desc
limit ?,?",[$offset,$page]);

        return $data;
    }

    /**
     * 获得附近的商品
     */
    public static function getNearWorks($uid, $lon, $lat, $page, $offset)
    {
        $data =  DB::select("SELECT  (  
          3959 * acos (  
          cos ( radians(?) )  
          * cos( radians( lat ) )  
          * cos( radians( lon ) - radians(?) )  
          + sin ( radians(?) )  
          * sin( radians( lat ) )  
        )  
      ) AS distance,
(select count(*) from lsj_comment_log b where a.id=b.worksid) as comment_num,
(select count(*) from lsj_order b where a.id=b.article_id and b.paystatus=1 and b.uid=?) as is_buy,
(select count(*) from lsj_follow b where a.author_uid=b.fuid and b.uid=?) as is_follow,
a.id,a.title,a.introduction,a.price,a.thumb,a.create_time,a.author_uid,a.reading,a.article_class,b.nickname as author,b.headimgurl,a.buy_class,
a.recommend,a.buy_num  
    FROM lsj_article a
    left join lsj_user b on a.author_uid=b.uid
	where a.lat!=0 and a.author_uid!=? and a.status=1 and cid=1  and a.is_onsale=1 and a.id!=25819 and a.buy_class=2
    HAVING distance < 20 
    ORDER BY distance 
limit ?,?",[$lat,$lon,$lat,$uid,$uid,$uid,$offset,$page]);

        return $data;
    }

    /**
     * 获得附近推荐商品
     */
    public static function getNearRecommendWorks($uid, $page, $offset)
    {
        $data =  DB::select("SELECT 0.1 distance,
(select count(*) from lsj_comment_log b where a.id=b.worksid) as comment_num,
(select count(*) from lsj_order b where a.id=b.article_id and b.paystatus=1 and b.uid=?) as is_buy,
(select count(*) from lsj_follow b where a.author_uid=b.fuid and b.uid=?) as is_follow,
a.id,a.title,a.introduction,a.price,a.thumb,a.create_time,a.author_uid,a.reading,a.article_class,b.nickname as author,b.headimgurl,a.buy_class,
a.recommend,a.buy_num  
    FROM lsj_article a
    left join lsj_user b on a.author_uid=b.uid
	where  a.author_uid!=? and a.status=1 and a.is_onsale=1 and a.id!=25819 and a.is_near>0 
limit ?,?",[$uid,$uid,$uid,$offset,$page]);
        
        if(!$data){
            $data = [];
        }
        return $data;
    }
	
	/**
     *分类页面
     * @param $uid
     * @param $page
     * @param $offset
     * @return mixed
     */
    public static function getOtherWorks($uid, $aclass = 0, $page, $offset)
    {
        $data =  DB::select("select
(select count(*) from lsj_praise_log b where b.articleid=a.id)  as praise_num,
(select count(*) from lsj_comment_log b where a.id=b.worksid) as comment_num,
(select count(*) from lsj_praise_log b where b.articleid=a.id and b.uid= ?) as is_praise,
(select count(*) from lsj_order b where a.id=b.article_id and b.paystatus=1 and b.uid=?) as is_buy,
(select count(*) from lsj_follow b where a.author_uid=b.fuid and b.uid=?) as is_follow,
a.id,a.title,a.introduction,a.price,a.thumb,a.create_time,a.author_uid,a.reading,a.article_class,b.nickname as author,b.headimgurl,
a.recommend,a.buy_class,a.buy_num
from lsj_article a
left join lsj_user b on a.author_uid=b.uid
where a.status=1 and cid=1 and article_class=?  and a.is_onsale=1 and a.id!=25819
order by a.recommend,a.reading desc,a.create_time desc
limit ?,?",[$uid,$uid,$uid,$aclass,$offset,$page]);

        return $data;
    }

    /**
     * 获取文章
     * @param array $info
     * @return mixed
     */
    public static function findArticle(array $info)
    {
        return ArticleModel::where($info)->first();
    }
	
	/**
     * 获得文章详情
     */
    public static function findArticleDetail($uid,$article_id)
    {
        $data =  DB::select("select
(select count(*) from lsj_praise_log b where b.articleid=a.id)  as praise_num,
(select count(*) from lsj_comment_log b where a.id=b.worksid) as comment_num,
(select count(*) from lsj_order b where a.id=b.article_id and b.paystatus=1) as order_num,
(select count(*) from lsj_praise_log b where b.articleid=a.id and b.uid= ?) as is_praise,
(select count(*) from lsj_order b where a.id=b.article_id and b.paystatus=1 and b.uid=?) as is_buy,
(select count(*) from lsj_follow b where a.author_uid=b.fuid and b.uid=?) as is_follow,
a.id,a.title,a.introduction,a.price,a.thumb,a.create_time,a.author_uid,a.reading,a.article_class,b.nickname as author,b.headimgurl,a.buy_class,
if(a.buy_class<>1,a.content,' ') as content,photo
from lsj_article a
left join lsj_user b on a.author_uid=b.uid
where a.status=1  and a.is_onsale=1 and a.id!=25819
and a.id = ?
order by a.create_time desc
limit 1",[$uid,$uid,$uid,$article_id]);

        return $data;
    }
	
	 /**
     * 根据uid获得用户文章列表
     */
    public static function userArticleList($uid, $page, $offset)
    {
        $data =  DB::select("select
(select count(*) from lsj_praise_log b where b.articleid=a.id)  as praise_num,
(select count(*) from lsj_comment_log b where a.id=b.worksid) as comment_num,
(select count(*) from lsj_order b where a.id=b.article_id and b.paystatus=1) as order_num,
(select count(*) from lsj_praise_log b where b.articleid=a.id and b.uid= ?) as is_praise,
(select count(*) from lsj_order b where a.id=b.article_id and b.paystatus=1 and b.uid=?) as is_buy,
a.id,a.title,a.introduction,a.price,a.thumb,a.create_time,a.author_uid,a.reading,a.article_class,b.nickname as author,
b.headimgurl,a.buy_class,a.is_onsale
from lsj_article a
left join lsj_user b on a.author_uid=b.uid
where a.status=1
and a.author_uid = ? and a.id!=25819
order by a.create_time desc
limit ?,?",[$uid,$uid,$uid,$offset,$page]);

        return $data;
    }

    /**
     * 小程序
     * @param $page
     * @param $offset
     * @return mixed
     */
	public static function xcxBestWorks($page, $offset)
    {
        $data =  DB::select("select
(select count(*) from lsj_praise_log b where b.articleid=a.id)  as praise_num,
(select count(*) from lsj_comment_log b where a.id=b.worksid) as comment_num,
(select count(*) from lsj_order b where a.id=b.article_id and b.paystatus=1) as order_num,
a.id,a.title,a.introduction,a.price,a.thumb,a.create_time,a.author_uid,a.reading,a.article_class,b.nickname as author,b.headimgurl
from lsj_article a
left join lsj_user b on a.author_uid=b.uid
where a.status=1 and cid=1 and a.is_onsale=1
order by a.create_time desc
limit ?,?",[$offset,$page]);

        return $data;
    }

    /**
     * 小程序
     * @param $page
     * @param $offset
     * @return mixed
     */
	public static function getXcxArticleDetail($id)
    {
        $data = DB::select("select nickname,headimgurl,a.create_time,a.content,a.pay_content,a.title 
from lsj_article a,lsj_user b 
where a.author_uid=b.uid and b.status=1 and a.cid=1 
and a.status=1 and a.price<=0.01 and a.id=?",[$id]);
        return $data;
    }

    /**
     * add文章
     */
    public static function createArticle(array $data)
    {
        {
            if($id = ArticleModel::insertGetId($data)){
                return $id;
            }
            return false;
        }
    }
	
	/**
     * 阅读量增加
     */
    public static function addArticleReading($article_id)
    {
        $result = DB::table('lsj_article')->where('id',$article_id)->increment('reading');
        return $result;
    }
}
        
        
        